<?php 
namespace app\system\admin;
use app\system\admin\Admin;
use think\Db;

/**
 *  @author Lin
 *  @time 2021/7/22
 */
class RedRate extends Admin
{
    protected function initialize()
    {
        parent::initialize();
        set_time_limit(0);
    }

    public function index(){
        if ($this->request->isAjax()) {
            $where  = $data = [];
            $page   = $this->request->param('page/d', 1);
            $limit  = $this->request->param('limit/d', 15);
            $keyword    = $this->request->param('keyword/s');
            $groupId    = $this->request->param('group_id/d');
            $bgTime = $this->request->param('bgTime/s');
            $endTime = $this->request->param('endTime/s');
            
            // if ($keyword) {
            //     $where[] = ['nickname','like','%'.$keyword.'%'];
            // }

            $list = Db::name('red_rate')->select();
            foreach($list as $key=>&$value){
                $value['is_send'] = Db::name('red_list')->where('rid',$value['id'])->count('id');
            }
            
            $data['data'] = $list;
            $data['count']  = Db::name('red_rate')->where($where)->count('id');
            $data['code']   = 0;

            return json($data);
        }
        return $this->fetch();
    }

    public function setRate(){
        $val = input('val');
        $field = input('field/s');
        $id = input('id/d');
        // $where[] = ['id','eq',$id];
        $where['id'] = $id;
        $data["$field"] = $val;
        // $data[] = [$field,'eq',$val];
        $rs = Db::name('red_rate')->where($where)->update($data);
        if($rs){
            okMsg();
        }
        else{
            errMsg('提交失败');
        }
    }
    public function data(){
        $today_start_time=strtotime(date("Y-m-d",time()));
        $today_end_time = $today_start_time+86399;
        $yes_start_time = $today_start_time-86400;
        $yes_end_time = $today_start_time-1;

        $id = input('id/d');
        $scene = Db::name('qrcode')->where('id',$id)->value('scene');
        // var_dump($scene);exit;
        $today_data = Db::query("select count(*) as sum from tdyh_playrecord where openid in(select openid from tdyh_userinfo where scene='$scene') and ctime>$today_start_time");
        $yes_data = Db::query("select count(*) as sum from tdyh_playrecord where openid in(select openid from tdyh_userinfo where scene='$scene') and ctime>$yes_start_time and ctime<$yes_end_time");
        $sum_data = Db::query("select count(*) as sum from tdyh_playrecord where openid in(select openid from tdyh_userinfo where scene='$scene')");
        
        $formData['today'] = $today_data[0]['sum'];
        $formData['yes'] = $yes_data[0]['sum'];
        $formData['sum'] = $sum_data[0]['sum'];
        // var_dump($formData);exit;
        $this->assign('formData', $formData);
        $template = $this->request->param('template', 'data');
        return $this->fetch($template);
        // var_dump($rs);exit;

    }
    public function user_export(){
        $where  = $data = [];
        $data = Db::name('userinfo')->where($where)->order('id desc')->select();
        if($data){
            foreach($data as $key=>&$value){
                $value['ctime'] = date('Y-m-d H:i:s',$value['ctime']);
                unset($value['id']);
                if($value['scene']){
                    switch($value['scene']){
                        case 'scene1':
                            $value['scene'] = '湖北商超办';
                            break;
                        case 'scene2':
                            $value['scene'] = '福建商超办';
                            break;
                        case 'scene3':
                            $value['scene'] = '江西商超办';
                            break;
                    }
                }
            }
        }
        // var_dump($data);exit;
        $setCellStyle = 'A1:E1';
        $title = ['用户openid','昵称','头像','登陆时间','来源'];

        $fileName = '用户基本数据.xls';

        $rs = $this->export_excel($title,$setCellStyle,$data,$fileName);
    }
    public function information(){
        if ($this->request->isAjax()) {
            $where  = $data = [];
           
            $bgTime = $this->request->param('bgTime/s');
            $endTime = $this->request->param('endTime/s');

            if($bgTime){
                $where[] = ['ctime','egt',strtotime($bgTime)];
            }
            if($endTime){
                $where[] = ['ctime','elt',strtotime($endTime)];
            }

            $usercount = Db::name('userinfo')->where($where)->count('id');
            $playcount = Db::name('playrecord')->where($where)->group('openid')->count('id');
            $prizecount = Db::name('prizerecord')->where($where)->count('id');
            $helpercount = Db::name('help_record')->where($where)->group('openid')->count('id');
            $where  =  [];
            $where[] = ['status','eq',1];
            $codecount = Db::name('prize_code')->where($where)->count('id');
            $list[0]['usercount'] = $usercount;
            $list[0]['playcount'] = $playcount;
            $list[0]['codecount'] = $codecount;
            $list[0]['prizecount'] = $prizecount;
            $list[0]['helpercount'] = $helpercount;
            

            $data['data'] = $list;
            $data['count']  =count($list);
            $data['code']   = 0;

            return json($data);
        }
        return $this->fetch();
    }

    public function playrecord(){
        $bgTime = 0;
        $endTime = 0;
        if ($this->request->isAjax()) {
            $where  = $data = [];
            $page   = $this->request->param('page/d', 1);
            $limit  = $this->request->param('limit/d', 15);
            $keyword    = $this->request->param('keyword/s');
            $groupId    = $this->request->param('group_id/d');
            $bgTime = $this->request->param('bgTime/s');
            $endTime = $this->request->param('endTime/s');
            
            if ($keyword) {
                $where[] = ['openid','like','%'.$keyword.'%'];
            }

            if($bgTime){
                $this->assign('bg_time',$bgTime);
                $where[] = ['ctime','egt',strtotime($bgTime)];
            }
            else{
                $this->assign('bg_time',0);
            }
            if($endTime){
                $this->assign('end_time',$endTime);
                $where[] = ['ctime','elt',strtotime($endTime)];
            }
            else{
                $this->assign('end_time',0);
            }

            $list = Db::name('playrecord')->where($where)->page($page)->order('id desc')->limit($limit)->select();
            if($list){
                foreach($list as $key=>&$value){
                    $uinfo = Db::name('userinfo')->where('openid',$value['openid'])->field('nickname,headimg,scene')->find();
                    if($uinfo){
                        $value['nickname'] = $uinfo['nickname'];
                        $value['headimg'] = $uinfo['headimg'];
                        if($uinfo['scene']){
                            switch($uinfo['scene']){
                                case 'scene1':
                                    $value['scene'] = '湖北商超办';
                                    break;
                                case 'scene2':
                                    $value['scene'] = '福建商超办';
                                    break;
                                case 'scene3':
                                    $value['scene'] = '江西商超办';
                                    break;
                                default:
                                    $value['scene'] = $uinfo['scene'];
                                break;
                            }
                            
                        }
                    }
                    else{
                        $value['nickname'] = '';
                        $value['headimg'] = '';
                        $value['scene'] = '';
                    }

                    if($value['voice_id']){
                        $value['voice_id'] = Db::name('voice_list')->where('id',$value['voice_id'])->value('readPath');
                        // var_dump($value['voice_id']);exit;
                    }
                }
                // var_dump($list);exit;
            }
            
            $data['data'] = $list;
            $data['count']  = Db::name('playrecord')->where($where)->count('id');
            $data['code']   = 0;

            return json($data);
        }
        $this->assign('bg_time',$bgTime);
        $this->assign('end_time',$endTime);

        return $this->fetch();
    }

    public function playrecord_export(){
        $where  = $data = [];
        $bg_time = input('bg_time/s');
        $end_time = input('end_time/s');
        
        
        if($bg_time){
            $where[] = ['ctime','egt',strtotime($bg_time)];
        }
        if($end_time){
            $where[] = ['ctime','elt',strtotime($end_time)];
        }
        
        $data = Db::name('playrecord')->where($where)->group('openid')->order('id desc')->select();
        if($data){
            foreach($data as $key=>&$value){
                $uinfo = Db::name('userinfo')->where('openid',$value['openid'])->field('nickname,headimg,scene')->find();
                if($uinfo){
                    $value['nickname'] = $uinfo['nickname'];
                    $value['headimg'] = $uinfo['headimg'];
                    if($uinfo['scene']){
                        switch($uinfo['scene']){
                            case 'scene1':
                                $value['scene'] = '湖北商超办';
                                break;
                            case 'scene2':
                                $value['scene'] = '福建商超办';
                                break;
                            case 'scene3':
                                $value['scene'] = '江西商超办';
                                break;
                            default:
                                $value['scene'] = $uinfo['scene'];
                            break;
                        }
                    }
                }
                else{
                    $value['nickname'] = '';
                    $value['headimg'] = '';
                    $value['scene'] = '';
                }

                if($value['voice_id']){
                    $value['voice_id'] = Db::name('voice_list')->where('id',$value['voice_id'])->value('readPath');
                    // var_dump($value['voice_id']);exit;
                }
                $value['ctime'] = date('Y-m-d H:i:s',$value['ctime']);
                unset($value['id']);
            }
        }
        // var_dump($data);exit;
        $setCellStyle = 'A1:H1';
        $title = ['用户openid','得分','录音地址','语音识别结果','参与时间','昵称','头像','来源'];

        $fileName = '用户参与记录.xls';

        $rs = $this->export_excel($title,$setCellStyle,$data,$fileName);
    }
    public function prizerecord_export(){
        $where  = $data = [];
        $data = Db::name('prizerecord')->where($where)->group('openid')->order('id desc')->select();
        if($data){
            foreach($data as $key=>&$value){
                unset($value['record_id']);
                unset($value['prize_type']);
                $uinfo = Db::name('userinfo')->where('openid',$value['openid'])->field('nickname,headimg,scene')->find();
                if($uinfo){
                    $value['nickname'] = $uinfo['nickname'];
                    $value['headimg'] = $uinfo['headimg'];
                    $value['scene'] = $uinfo['scene'];
                }
                else{
                    $value['nickname'] = '';
                    $value['headimg'] = '';
                    $value['scene'] = '';
                }

                $value['ctime'] = date('Y-m-d H:i:s',$value['ctime']);
                unset($value['id']);
            }
        }
        // var_dump($data);exit;
        $setCellStyle = 'A1:G1';
        $title = ['用户openid','获奖时间','奖品','兑换码','昵称','头像','来源'];

        $fileName = '中奖数据.xls';

        $rs = $this->export_excel($title,$setCellStyle,$data,$fileName);
    }
    
    public function prizerecord(){
        if ($this->request->isAjax()) {
            $where  = $data = [];
            $page   = $this->request->param('page/d', 1);
            $limit  = $this->request->param('limit/d', 15);
            $keyword    = $this->request->param('keyword/s');
            $groupId    = $this->request->param('group_id/d');
            $bgTime = $this->request->param('bgTime/s');
            $endTime = $this->request->param('endTime/s');
            
            if ($keyword) {
                $where[] = ['openid','like','%'.$keyword.'%'];
            }

            $list = Db::name('prizerecord')->where($where)->page($page)->order('id desc')->limit($limit)->select();
            if($list){
                foreach($list as $key=>&$value){
                    $uinfo = Db::name('userinfo')->where('openid',$value['openid'])->field('nickname,headimg,scene')->find();
                    if($uinfo){
                        $value['nickname'] = $uinfo['nickname'];
                        $value['headimg'] = $uinfo['headimg'];
                        if($uinfo['scene']){
                            switch($uinfo['scene']){
                                case 'scene1':
                                    $value['scene'] = '湖北商超办';
                                    break;
                                case 'scene2':
                                    $value['scene'] = '福建商超办';
                                    break;
                                case 'scene3':
                                    $value['scene'] = '江西商超办';
                                    break;
                            }
                        }
                    }
                    else{
                        $value['nickname'] = '';
                        $value['headimg'] = '';
                        $value['scene'] = '';
                    }

                }
            }
            
            $data['data'] = $list;
            $data['count']  = Db::name('prizerecord')->where($where)->count('id');
            $data['code']   = 0;

            return json($data);
        }
        return $this->fetch();
    }

    public function prize(){
        if ($this->request->isAjax()) {
            $where  = $data = [];
            $page   = $this->request->param('page/d', 1);
            $limit  = $this->request->param('limit/d', 15);
            $keyword    = $this->request->param('keyword/s');
            $groupId    = $this->request->param('group_id/d');
            $bgTime = $this->request->param('bgTime/s');
            $endTime = $this->request->param('endTime/s');
            
            // if ($keyword) {
            //     $where[] = ['nickname','like','%'.$keyword.'%'];
            // }

            $list = Db::name('prize')->select();
            foreach($list as $key=>&$value){
                $value['is_send'] = Db::name('prizerecord')->where('prize_type',$value['prize_type'])->count('id');
                $value['rest_code'] = Db::name('prize_code')->where(array('type'=>$value['prize_type'],'status'=>0))->count('id');
            }
            
            $data['data'] = $list;
            $data['count']  = Db::name('prize')->where($where)->count('id');
            $data['code']   = 0;

            return json($data);
        }
        return $this->fetch();
    }


    public function qrcode(){
        $bgTime = 0;
        $endTime = 0;
        if ($this->request->isAjax()) {
            $where  = $data = [];
            $page   = $this->request->param('page/d', 1);
            $limit  = $this->request->param('limit/d', 15);
            $keyword    = $this->request->param('keyword/s');
            $groupId    = $this->request->param('group_id/d');
            $bgTime = $this->request->param('bgTime/s');
            $endTime = $this->request->param('endTime/s');

            
            
            if ($keyword) {
                $where[] = ['scene','like','%'.$keyword.'%'];
            }

            $list = Db::name('qrcode')->where($where)->page($page)->order('id desc')->limit($limit)->select();

            if($bgTime){
                $this->assign('bg_time',$bgTime);
                // $where[] = ['ctime','egt',strtotime($bgTime)];
                $today_start_time = strtotime($bgTime);
            }
            else{
                $today_start_time=strtotime(date("Y-m-d",time()));
                $this->assign('bg_time',0);
            }
            if($endTime){
                $today_end_time = strtotime($endTime);
                $this->assign('end_time',$endTime);
                // $where[] = ['ctime','elt',strtotime($endTime)];
            }
            else{
                $today_end_time = $today_start_time+86399;
                $this->assign('end_time',0);
            }
            // $today_start_time=strtotime(date("Y-m-d",time()));
            // $today_end_time = $today_start_time+86399;
            $yes_start_time = $today_start_time-86400;
            $yes_end_time = $today_start_time-1;
            foreach($list as $key=>&$value){
                $scene = $value['scene'];
                // var_dump($scene);exit;
                $today = Db::query("select count(distinct(openid)) as sum from tdyh_playrecord where openid in(select openid from tdyh_userinfo where scene='$scene') and ctime>$today_start_time and ctime<$today_end_time");
                // $yes = Db::query("select count(*) as sum from tdyh_playrecord where openid in(select openid from tdyh_userinfo where scene='$scene') and ctime>$yes_start_time and ctime<$yes_end_time");
                $sum = Db::query("select count(distinct(openid)) as sum from tdyh_playrecord where openid in(select openid from tdyh_userinfo where scene='$scene')");
                // var_dump($today);exit;
                $value['today'] = $today[0]['sum'];
                // $value['yes'] = $yes[0]['sum'];
                $value['sum'] = $sum[0]['sum'];
            }
            // foreach($list as $key=>&$value){
            //     if($value['scene']){
            //         switch($value['scene']){
            //             case 'scene1':
            //                 $value['scene'] = '湖北商超办';
            //                 break;
            //             case 'scene2':
            //                 $value['scene'] = '福建商超办';
            //                 break;
            //             case 'scene3':
            //                 $value['scene'] = '江西商超办';
            //                 break;
            //         }
            //     }
            // }
            $data['data'] = $list;
            $data['count']  = Db::name('qrcode')->count('id');
            $data['code']   = 0;

            return json($data);
        }
        $this->assign('bg_time',$bgTime);
        $this->assign('end_time',$endTime);
        return $this->fetch();
    }
    public function qrcode_export_excel(){
        $where  = $data = [];
        $bg_time = input('bg_time/s');
        $end_time = input('end_time/s');
        
        
        if($bg_time){
            // $where[] = ['ctime','egt',strtotime($bg_time)];
            $today_start_time = strtotime($bg_time);
        }
        else{
            $today_start_time=strtotime(date("Y-m-d",time()));
        }
        if($end_time){
            $today_end_time = strtotime($end_time);
            // $where[] = ['ctime','elt',strtotime($endTime)];
        }
        else{
            $today_end_time = $today_start_time+86399;
        }
        $data = Db::name('qrcode')->where($where)->field('scene')->order('id desc')->select();
        // $today_start_time=strtotime(date("Y-m-d",time()));
        // $today_end_time = $today_start_time+86399;
        $yes_start_time = $today_start_time-86400;
        $yes_end_time = $today_start_time-1;
        foreach($data as $key=>&$value){
            $scene = $value['scene'];
            // var_dump($scene);exit;
            $today = Db::query("select count(distinct(openid)) as sum from tdyh_playrecord where openid in(select openid from tdyh_userinfo where scene='$scene') and ctime>$today_start_time and ctime<$today_end_time");
            // $yes = Db::query("select count(*) as sum from tdyh_playrecord where openid in(select openid from tdyh_userinfo where scene='$scene') and ctime>$yes_start_time and ctime<$yes_end_time");
            $sum = Db::query("select count(distinct(openid)) as sum from tdyh_playrecord where openid in(select openid from tdyh_userinfo where scene='$scene')");

            $value['today'] = $today[0]['sum'];
            // $value['yes'] = $yes[0]['sum'];
            $value['sum'] = $sum[0]['sum'];
        }
        // var_dump($data);exit;
        $setCellStyle = 'A1:D1';
        $title = ['场景','今日参与量','参与总量'];

        $fileName = '带参二维码参与数据.xls';

        $rs = $this->export_excel($title,$setCellStyle,$data,$fileName);
    }
    public function add(){
        if($this->request->isPost()){
            $list = $this->request->post();
            $data['describe'] = $list['describe'];
            if(!$data['describe']){
                return $this->error('描述不能为空');
            }
            $scene_check = Db::name('qrcode')->count('id');
            $scene_check += 1;
            $scene = "scenes".$scene_check;
            $data['qrcode'] = $this->getQrcode($scene);
            $data['ctime'] = time();
            $data['scene'] = $scene;
            $rs = Db::name('qrcode')->insert($data);
            if($rs){
                return $this->success('提交成功');
            }
            else{
                return $this->error('提交失败，请重试');
            }
        }
        else{
            $template = $this->request->param('template', 'form');
            return $this->fetch($template);
        }
    }
    public function getQrcodeList(){
        for($i=1;$i<=40;$i++){
            $scene_check = Db::name('qrcode')->count('id');
            $scene_check += 1;
            $scene = "scenes".$scene_check;
            $data['qrcode'] = $this->getQrcode($scene);
            $data['ctime'] = time();
            $data['scene'] = $scene;
            $rs = Db::name('qrcode')->insert($data);
        }
        
        
    }
    public function getQrcode($scene=''){
        if(!$scene){
            return false;
        }
        $scene_check = Db::name('qrcode')->where('scene',$scene)->value('id');
        if($scene_check){
            return false;
        }
        $access_token = $this->getAccessToken();
        $url = "https://api.weixin.qq.com/cgi-bin/wxaapp/createwxaqrcode?access_token=".$access_token;
        $param = json_encode(array("path"=>"pages/home/index?scene=".$scene,"width"=> 150));
        //POST参数
        $result = $this->httpRequest($url,$param,"POST");
        //生成二维码
        file_put_contents("../public/upload/qrcode/".$scene.".png",$result);
        $imgUrl = "https://partner.starrydigital.cn/upload/qrcode/".$scene.".png";
        return $imgUrl;
    }

    //把请求发送到微信服务器换取二维码
    public function httpRequest($url,$data='',$method='GET'){
        $curl = curl_init(); 
        curl_setopt($curl, CURLOPT_URL,$url); 
        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, 0); 
        curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, 0); 
        curl_setopt($curl, CURLOPT_USERAGENT,$_SERVER['HTTP_USER_AGENT']); 
        curl_setopt($curl, CURLOPT_FOLLOWLOCATION, 1); 
        curl_setopt($curl, CURLOPT_AUTOREFERER, 1); 
        if($method=='POST')
        {
            curl_setopt($curl, CURLOPT_POST, 1);
            if ($data !='')
            {
                curl_setopt($curl, CURLOPT_POSTFIELDS,$data); 
            }
        }
    
        curl_setopt($curl, CURLOPT_TIMEOUT, 30); 
        curl_setopt($curl, CURLOPT_HEADER, 0); 
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1); 
        $result = curl_exec($curl); 
        curl_close($curl); 
        return $result;
    }
    public function getAccessToken(){
        $token = Db::name('token')->where('tag','tdyh_xcx')->find();
        if(!$token){
            $token_arr = $this->updateToken();
            // var_dump($token_arr);exit;
            $data['value'] = $token_arr['access_token'];
            $data['expire_time'] = $token_arr['expires_in'];
            $data['tag'] = 'tdyh_xcx';
            $data['create_time'] = time();
            $rs = Db::name('token')->insert($data);
            return $token_arr['access_token'];
        }
        else{
            $time = time();
            if($token['expire_time']<$time){
                // token已过期,重新获取
                $token_arr = $this->updateToken();
                $data['value'] = $token_arr['access_token'];
                $data['expire_time'] = $token_arr['expires_in'];
                $data['create_time'] = time();
                $rs = Db::name('token')->where('tag','tdyh_xcx')->update($data);
                return $token_arr['access_token'];
            }
            else{
                if($token['value']){
                    return $token['value'];
                }
                else{
                    // 重新获取
                    $token_arr = $this->updateToken();
                    $data['value'] = $token_arr['access_token'];
                    $data['expire_time'] = $token_arr['expires_in'];
                    $data['create_time'] = time();
                    $rs = Db::name('token')->where('tag','tdyh_xcx')->update($data);
                    return $token_arr['access_token'];
                }
            }
        }
    }

    public function updateToken(){
        $appid = config('appid');
        $appsecret = config('appscrect');
        $url = "https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=".$appid."&secret=".$appsecret;
        // var_dump($url);exit;
        $rs = file_get_contents($url);
        $result_arr = json_decode($rs,true);
        if(isset($result_arr['access_token'])){
            return array('access_token'=>$result_arr['access_token'],'expires_in'=>$result_arr['expires_in']);
        }
        else{
            // var_dump($result_arr);exit;
            return false;
        }
    }
    /**
     * 导出excel
     */
    public function export_excel($title,$setCellStyle,$data,$fileName)
    {
    
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            $spreadsheet->getActiveSheet()->getStyle($setCellStyle)->getFont()->setBold(true)->setName('Arial')->setSize(10);
            //设置标题栏背景颜色
            $spreadsheet->getActiveSheet()->getStyle($setCellStyle)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('#FF7F50');


            //设置第10行行高为100pt。
            $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

            //如果需要自动计算列宽，可以这样：
            //$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

            //设置默认列宽为15。
            $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);

            //设置水平对齐
            // $styleArray = [
            //     'alignment' => [
            //       'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            //     ],
            //   ];

            // $spreadsheet->getStyle('A1')->applyFromArray($styleArray);

            //设置单元格内容
            $titCol = 'A';
            foreach ($title as $key => $value) {
                //单元格内容写入
                $sheet->setCellValue($titCol . '1', $value);
                $titCol++;
            }
            
            $row = 2; 
            foreach ($data as $item) {
                $dataCol = 'A';
                foreach ($item as $value) {
                    //单元格内容写入
                    $sheet->setCellValue($dataCol . $row, $value);
                    $dataCol++;
                }
                $row++;
            }

            //保存
            $writer = IOFactory::createWriter($spreadsheet, 'Xls');
            
            ob_end_clean();
            
            //输出到浏览器
            header('Content-type: text/html; charset=utf-8');
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename=' . $fileName);
            header('Cache-Control: max-age=0');
            $writer->save('php://output');
    }
}
?>

